Mega Code Archive
Aggregate Functions
1) And to find the lowest commission that any of the sales staff is earning, use this
2) Average length
3) AVG() Function averages the values returned by a specified expression
4) Calculate the maximum value by using the MAX() function
5) Categorizing Non-Categorical Data
6) Controlling String Case Sensitivity for MIN( ) and MAX( )
7) COUNT and GROUP BY
8) Count by group
9) Count duplicate records
10) Count each value and see which one is most common
11) Count number of rows containing duplicated names
12) Count the total number of rows
13) Count them directly using SUM(ISNULL(score))
14) COUNT([DISTINCT] {expression })
15) COUNT(expr) doesnt count NULL values is useful when producing multiple counts from the same set of values
16) Counting
17) Counting and Identifying Duplicates
18) Counting Missing Values
19) Determine the population range
20) Do calculation with aggregate function
21) Eliminating Duplicates from a Query Result
22) Find the average sales amount per sales quarter
23) Finding Smallest or Largest Summary Values
24) Finding Values Associated with Minimum and Maximum Values
25) Finds the size of the largest message sent between each pair of sender and recipient values listed
26) Generating Frequency Distributions
27) Get the average price
28) Get the maximum price and minimum price
29) Get the number of items for each type of wood
30) Get the number of woods for each type of item
31) GROUP_CONCAT demo
32) GROUP_CONCAT groups together character strings
33) How many days did Suzi drive
34) How many messages were sent by each message sender
35) How many miles did the drivers in the mytable table travel What was the average miles traveled per day
36) How many states did the United States consist of at the beginning of the 20th century
37) How many times did drivers travel more than 200 miles in a day
38) How much you paid for each authors books, in total and on average
39) MIN( ) and MAX( ) also work with expressions or values that are derived from column values
40) Or to count weekend versus weekday trips
41) Put the COUNT( ) expression in a HAVING clause instead
42) Queries counting duplicate records have the following form
43) Relative frequency distributions
44) Removing Duplicates of a Particular Row
45) Returning the Average, Minimum, and Total Values with AVG( ), MIN( ), and SUM( )
46) Returning the Maximum Value with MAX()
47) Returns the minimum value from a group of values
48) SET @@GROUP_CONCAT_MAX_LEN=7
49) Some aggregate functions can still produce NULL as a result
50) Standard deviation
51) STDDEV_SAMP amount
52) Sum by group
53) SUM() function returns the sum of the expression
54) SUM() the commission
55) Summaries and NULL Values
56) Summarizing with MIN( ) and MAX( )
57) The different forms of COUNT( ) can be very useful for counting missing values
58) The number of votes for each programming language is determined with SUM(IF( ))
59) To compute the runners average speed at the end of each stage
60) To determine how many drivers were on the road and how many miles were driven each day
61) To display an average value of zero in that case, modify the query to test the value of AVG( ) with IFNULL( )
62) To make bstr not case sensitive, you can convert the values to a given lettercase
63) To obtain the state name associated with the maximum population
64) To produce a count-per-author summary that includes even authors with no books in the book table, use a LEFT J
65) To see which names are duplicated in the cat_mailing table, use a summary query that displays the non-unique v
66) To select values that lie more than three standard deviations from the mean
67) Total number of the table vs unique values count
68) Tracking Down Duplicates
69) Use MIN( ) or MAX( ) With a GROUP BY clause
70) Use the CONCAT( ) expression with MAX( ) to find the value with the largest population part
71) VAR_SAMP amount
72) Variance value
73) What are the first and last state names, lexically speaking
74) What are the lowest and highest U S state populations
75) What are the shortest and longest trips in the mytable table
76) What is the total amount of mail traffic and the average size of each message
77) What is the total population of the United States